"""
Case Type   : CLEAN CONNECTION
Case Name   : 权限验证，系统管理员用户进行清理，清理其他用户连接成功
Create At   : 2022-05-10
Owner       : liu-tong-8848
Description :
    1、创建数据库
    2、创建用户
    3、线程1连接数据库
    4、线程2连接数据库
    5、线程3连接数据库
    6、线程4连接数据库
    7、线程5执行清理操作
    8、线程6执行清理操作
    9、线程7执行清理操作
    10、线程8执行清理操作
    11、清理环境
Expect      :
    1、创建数据库，expect: 成功
    2、创建用户，expect: 成功
    3、线程1连接数据库，expect: 成功
    4、线程2连接数据库，expect: 成功
    5、线程3连接数据库，expect: 成功
    6、线程4连接数据库，expect: 成功
    7、线程5执行清理操作，expect: 失败
    8、线程6执行清理操作，expect: 成功
    9、线程7执行清理操作，expect: 成功
    10、线程8执行清理操作，expect: 成功
    11、清理环境，expect: 成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
import time

from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CleanConnection(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'd_clean_0026'
        self.u_name1 = 'u_clean_0026'
        self.u_name2 = 'u_clean_0026_1'
        self.u_name3 = 'u_clean_0026_2'
        self.u_name4 = 'u_clean_0026_3'
        self.flag = 'CLEAN CONNECTION'
        self.err_flag = 'connection to server was lost'

    def test_main(self):
        step_txt = '----step1: 创建数据库，expect: 成功----'
        self.log.info(step_txt)
        create_sql = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.CREATE_DATABASE_SUCCESS) == 1,
            '执行失败:' + step_txt)

        step_txt = '----step2: 创建用户，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop user if exists {self.u_name1} cascade;" \
            f"create user {self.u_name1} sysadmin " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name2} cascade;" \
            f"create user {self.u_name2} createrole " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name3} cascade;" \
            f"create user {self.u_name3} auditadmin " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name4} cascade;" \
            f"create user {self.u_name4} " \
            f"password '{macro.COMMON_PASSWD}';"
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.CREATE_ROLE_SUCCESS_MSG) == 4,
            '执行失败:' + step_txt)

        step3_txt = '----step3: 线程1连接数据库;expect:成功----'
        self.log.info(step3_txt)
        sql_cmd1 = 'select sysdate;select pg_sleep(31);select sysdate;'
        excute_cmd1 = ' '
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd1, excute_cmd1, self.db_name))
        session1.setDaemon(True)
        session1.start()

        step4_txt = '----step4: 线程2连接数据库;expect:成功----'
        self.log.info(step4_txt)
        sql_cmd2 = 'select sysdate;select pg_sleep(60);select sysdate;'
        excute_cmd2 = f'-U {self.u_name2} -W "{macro.COMMON_PASSWD}" '
        session2 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd2, excute_cmd2, self.db_name))
        session2.setDaemon(True)
        session2.start()

        step5_txt = '----step5: 线程3连接数据库;expect:成功----'
        self.log.info(step5_txt)
        sql_cmd3 = 'select sysdate;select pg_sleep(60);select sysdate;'
        excute_cmd3 = f'-U {self.u_name3} -W "{macro.COMMON_PASSWD}" '
        session3 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd3, excute_cmd3, self.db_name))
        session3.setDaemon(True)
        session3.start()

        step6_txt = '----step6: 线程4连接数据库;expect:成功----'
        self.log.info(step6_txt)
        sql_cmd4 = 'select sysdate;select pg_sleep(60);select sysdate;'
        excute_cmd4 = f'-U {self.u_name4} -W "{macro.COMMON_PASSWD}" '
        session4 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd4, excute_cmd4, self.db_name))
        session4.setDaemon(True)
        session4.start()
        time.sleep(5)

        step7_txt = '----step7: 线程5执行清理操作，expect: 清理失败----'
        self.log.info(step7_txt)
        sql_cmd5 = f'clean connection to all force ' \
                   f'to user {self.pri_root.ssh_user};'
        excute_cmd5 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session5 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd5, excute_cmd5, self.db_name))
        session5.setDaemon(True)
        session5.start()

        step8_txt = '----step8: 线程6执行清理操作，expect: 清理成功----'
        self.log.info(step8_txt)
        sql_cmd6 = f'clean connection to all force to user {self.u_name2};'
        excute_cmd6 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session6 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd6, excute_cmd6, self.db_name))
        session6.setDaemon(True)
        session6.start()

        step9_txt = '----step9: 线程7执行清理操作，expect: 清理成功----'
        self.log.info(step9_txt)
        sql_cmd7 = f'clean connection to all force to user {self.u_name3};'
        excute_cmd7 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session7 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd7, excute_cmd7, self.db_name))
        session7.setDaemon(True)
        session7.start()

        step10_txt = '----step10: 线程8执行清理操作，expect: 清理成功----'
        self.log.info(step10_txt)
        sql_cmd8 = f'clean connection to all force to user {self.u_name4};'
        excute_cmd8 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session8 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd8, excute_cmd8, self.db_name))
        session8.setDaemon(True)
        session8.start()

        self.log.info("----session1执行结果----")
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn(self.err_flag, session1_result,
                      '执行失败:' + step3_txt)

        self.log.info("----session2执行结果----")
        session2.join()
        session2_result = session2.get_result()
        self.log.info(session2_result)
        self.assertIn(self.err_flag, session2_result,
                      '执行失败:' + step4_txt)

        self.log.info("----session3执行结果----")
        session3.join()
        session3_result = session3.get_result()
        self.log.info(session3_result)
        self.assertIn(self.err_flag, session3_result,
                      '执行失败:' + step5_txt)

        self.log.info("----session4执行结果----")
        session4.join()
        session4_result = session4.get_result()
        self.log.info(session4_result)
        self.assertIn(self.err_flag, session4_result,
                      '执行失败:' + step6_txt)

        self.log.info("----session5执行结果----")
        session5.join()
        session5_result = session5.get_result()
        self.log.info(session5_result)
        self.assertIn(f'non-thread pool mode not completed.',
                      session5_result, '执行失败:' + step7_txt)

        self.log.info("----session6执行结果----")
        session6.join()
        session6_result = session6.get_result()
        self.log.info(session6_result)
        self.assertIn(self.flag, session6_result,
                      '执行失败:' + step8_txt)

        self.log.info("----session7执行结果----")
        session7.join()
        session7_result = session7.get_result()
        self.log.info(session7_result)
        self.assertIn(self.flag, session7_result,
                      '执行失败:' + step9_txt)

        self.log.info("----session8执行结果----")
        session8.join()
        session8_result = session8.get_result()
        self.log.info(session8_result)
        self.assertIn(self.flag, session8_result,
                      '执行失败:' + step10_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step11: 删除数据库和用户----'
        self.log.info(step_txt)
        drop_sql = f'drop database if exists {self.db_name};' \
                   f'drop user if exists {self.u_name1} cascade;' \
                   f'drop user if exists {self.u_name2} cascade;' \
                   f'drop user if exists {self.u_name3} cascade;' \
                   f'drop user if exists {self.u_name4} cascade;'
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)

        self.log.info(f'-----{os.path.basename(__file__)} end-----')
